﻿Imports System.Data.SqlClient

Public Class frmTargetCompanyEntry

    Sub FillBudget()
        Try
            If Me.CombBranch.SelectedIndex = -1 OrElse Me.CombBranch.SelectedIndex = -1 Then
                Exit Sub
            End If

            Me.Cursor = Cursors.WaitCursor

            Me.GridBudget.Rows.Clear()

            Dim cmd As New SqlCommand("Select Distinct ClassName," & _
                                      "IsNull(dbo.GetPremiumBudget(ClassName,N'" & Me.CombYear.SelectedItem & _
                                      "',N'" & Me.CombBranch.SelectedItem & "'),0) Commission," & _
                                      "IsNull(dbo.GetCommissionBudget(ClassName,N'" & Me.CombYear.SelectedItem & _
                                      "',N'" & Me.CombBranch.SelectedItem & "'),0) Premium " & _
                                      "From InsurClasses", cnn)
            Dim Reader As SqlDataReader

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridBudget.Rows.Add(New String() {Reader.Item("ClassName"), CDbl(Reader.Item("Commission")).ToString("N2"), _
                                                     CDbl(Reader.Item("Premium")).ToString("N2")})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub CombYear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombYear.SelectedIndexChanged
        Me.GridBudget.Rows.Clear()
        FillBudget()
    End Sub

    Private Sub CombJeddah_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombBranch.SelectedIndexChanged
        Me.GridBudget.Rows.Clear()
        FillBudget()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Me.CombYear.SelectedIndex = -1
        Me.CombBranch.SelectedIndex = -1
        Me.GridBudget.Rows.Clear()
    End Sub

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombBranch.Items.Clear()
           
            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
             Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Me.CombYear.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombYear, "Please select a valid year from the list")

        ElseIf Me.CombBranch.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombBranch, "Please select a valid year from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cmd.Connection = cnn

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Delete old data
                cmd.CommandText = "Delete From UnderwritingBudget Where Year=N'" & Me.CombYear.SelectedItem & _
                                  "' and Branch=N'" & Me.CombBranch.SelectedItem & "'"
                cmd.ExecuteNonQuery()

                'Save new year budget
                For Each row As DataGridViewRow In Me.GridBudget.Rows
                    cmd.CommandText = "Insert Into UnderwritingBudget (Year,Branch,Class,Premium,Commission) " & _
                                      "Values (@Year,@Branch,@Class,@Premium,@Commission)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@Year", Me.CombYear.SelectedItem)
                    cmd.Parameters.AddWithValue("@Branch", Me.CombBranch.SelectedItem)
                    cmd.Parameters.AddWithValue("@Class", row.Cells(0).Value)
                    cmd.Parameters.AddWithValue("@Premium", CDbl(row.Cells(1).Value))
                    cmd.Parameters.AddWithValue("@Commission", CDbl(row.Cells(2).Value))
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridBudget_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridBudget.CellEndEdit
        Try
            Me.GridBudget.CurrentCell.Value = CDbl(Me.GridBudget.CurrentCell.Value).ToString("N2")
        Catch ex As Exception
            Me.GridBudget.CurrentCell.Value = "0.00"
        End Try
    End Sub


    Private Sub frmTargetCompanyEntry_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
    End Sub
End Class